package com.alpha.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.NamingException;

import com.alpha.bom.Professor;
import com.alpha.dao.interfaces.IProfessorDao;

public class ProfessorDao extends AlphaDao implements IProfessorDao {
	public Professor retrieveProfessor(int professorId) throws SQLException, NamingException {
		Professor professor = null;
		this.connect();
		try {
			String select = "SELECT * FROM tbl_user WHERE user_id="+professorId+" AND user_is_admin=1";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int id = this.resultSet.getInt("user_id");
				String password = this.resultSet.getString("user_password");
				String firstName = this.resultSet.getString("user_first_name");
				String lastName = this.resultSet.getString("user_last_name");
				professor = new Professor();
				professor.setUserId(id);
				professor.setPassword(password);
				professor.setFirstName(firstName);
				professor.setLastName(lastName);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			this.disconnect();
		}
		
		return professor;
	}
	
	public ArrayList<Professor> retrieveProfessors() throws SQLException, NamingException {
		ArrayList<Professor> professors = new ArrayList<Professor>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_user WHERE user_is_admin=1";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int id = this.resultSet.getInt("user_id");
				String password = this.resultSet.getString("user_password");
				String firstName = this.resultSet.getString("user_first_name");
				String lastName = this.resultSet.getString("user_last_name");
				Professor professor = new Professor();
				professor.setUserId(id);
				professor.setPassword(password);
				professor.setFirstName(firstName);
				professor.setLastName(lastName);
				professors.add(professor);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			this.disconnect();
		}
		
		return professors;
	}

	public boolean insertProfessor(Professor professor) throws SQLException, NamingException {
		boolean success = false;
		this.connect();
		String insert1 = "INSERT INTO tbl_user(user_id, user_password, user_first_name, user_last_name, user_is_admin) VALUES (?,?,?,?,?)";
		String insert2 = "INSERT INTO tbl_user_role(user_id, role_name) VALUES (?,?)";
		try {
			this.preparedStatement = this.connection.prepareStatement(insert1);
			this.preparedStatement.setInt(1, professor.getUserId());
			this.preparedStatement.setString(2, professor.getPassword());
			this.preparedStatement.setString(3, professor.getFirstName());
			this.preparedStatement.setString(4, professor.getLastName());
			this.preparedStatement.setInt(5, 1);
			int rows = this.preparedStatement.executeUpdate();		
			
			this.preparedStatement = this.connection.prepareStatement(insert2);
			this.preparedStatement.setInt(1, professor.getUserId());
			this.preparedStatement.setString(2, "admin");
			rows += this.preparedStatement.executeUpdate();
			
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean updateProfessor(Professor professor) throws SQLException, NamingException {
		boolean success = false;
		this.connect();
		try {
			String update = "UPDATE tbl_user SET user_first_name=?, user_last_name=?, user_password=? WHERE user_id=?";
			this.preparedStatement = this.connection.prepareStatement(update);
			this.preparedStatement.setString(1, professor.getFirstName());
			this.preparedStatement.setString(2, professor.getLastName());
			this.preparedStatement.setString(3, professor.getPassword());
			this.preparedStatement.setInt(4, professor.getUserId());
			int rows = this.preparedStatement.executeUpdate();
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}
}
